#import <Foundation/Foundation.h>
#import <objc/runtime.h>
#import <sqlite3.h>
#import <UIKit/UIApplication.h>
#import <LockInfo/Plugin.h>

#import "framework/FWCommon.h"
#import "framework/FWApplication.h"
#import "framework/FWDBAccess.h"
#import "framework/FWPreferences.h"

#import "PICommon.h"
#import "PIIntegration.h"
#import "PIPlugin.h"
#import "PIContextObject.h"
#import "PITodoStatus.h"
#import "PIContext.h"

#import "PITaskIntegration.h"
#import "PITask.h"


static NSString* const kFieldNameEndDate = @"xxx_enddate";
static NSString* const kFieldNameContextStatus = @"xxx_contextstat";
static NSString* const kFieldNameChildCount = @"xxx_children";

static NSInteger const kSQLBasicTaskList = 1;
static NSInteger const kSQLAddChildData = 2;
static NSInteger const kSQLAugmentedTask = 3;
static NSInteger const kSQLArrayOfContexts = 4;
static NSInteger const kSQLMaxTaskID = 5;
static NSInteger const kSQLNotes = 6;

static NSInteger const kMaxSearchItems = 50;


@implementation PITaskIntegration


// ******************************************************************************************
#pragma mark -
#pragma mark Fetching a list of Tasks

+ (void)pi2addSortField:(NSMutableString*)current field:(int)field ascending:(BOOL)ascending invert:(BOOL)invert prefs:(FWPreferences*)prefs
{
    DebugLog(@"LI:PITodo: Adding PI2 SQL sort field, current=%@; field=%i; ascend=%i; invert=%i", current, field, ascending, invert);        
    
    if (field == SortFieldOptionsNone)
        return;
    
    NSMutableString* fieldName;
    BOOL atStart;
    switch (field) {
        case SortFieldOptionsImportance:
            DebugLog(@"LI:PITodo: Adding importance sort field");
            fieldName = [NSMutableString stringWithCapacity:80];
            if (invert)
                [fieldName setString:@"case T.ZPRIORITYINTEGER when 0 then 99 else T.ZPRIORITYINTEGER end"];
            else
                [fieldName setString:@"T.ZPRIORITYINTEGER"];
            
            [fieldName appendFormat:@"/*Importance[%i]*/", invert];
            break;
            
        case SortFieldOptionsPriority:
            // Sort by initial letter if present or '-/~' if starts with a number - so numbers precede or follow letters
            // depending upon the invert flag state. If no value at all then use '~~' to ensure no fc value is always
            // at the end of the list.
            DebugLog(@"LI:PITodo: Adding priority sort field");
            fieldName = [NSMutableString stringWithCapacity:240];
            [fieldName appendFormat:
             @"case when ifnull(T.ZFCPRIORITY,'')='' then '~~'"
             " when abs(substr(T.ZFCPRIORITY,1,1))>0 then '%@'"
             " else substr(T.ZFCPRIORITY,1,1) end", invert ? @"~" : @"-"];
            if (!ascending)
                [fieldName appendString:@" DESC"];
            
            [fieldName appendString:
             @", case when abs(substr(T.ZFCPRIORITY,1,1))=0 then abs(substr(T.ZFCPRIORITY,2)) else abs(T.ZFCPRIORITY) end"];
            
            [fieldName appendFormat:@"/*Priority[%i]*/", invert];
            break;
            
        case SortFieldOptionsAction:
            DebugLog(@"LI:PITodo: Adding action sort field");
            fieldName = [NSMutableString stringWithCapacity:80];
            if (invert)
                [fieldName setString:@"case T.ZACTIONINTEGER when 0 then 99 else T.ZACTIONINTEGER end"];
            else
                [fieldName setString:@"T.ZACTIONINTEGER"];
            
            [fieldName appendFormat:@"/*Action[%i]*/", invert];
            break;
            
        case SortFieldOptionsDueDate:
            // PI stores end times as local rather than UTC so we need to convert the local time to UTC
            // in order to compare with an end date. This will be a hack as srictly we need the number of
            // seconds offset on the given day to account for DST. That's the way that the display functions
            // work.
            DebugLog(@"LI:PITodo: Adding due date sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            if (invert)
                [fieldName appendFormat:@"ifnull(%@,9999999999)", kFieldNameEndDate];
            else
                [fieldName appendFormat:@"%@", kFieldNameEndDate];
            
            [fieldName appendFormat:@"/*Due[%i]*/", invert];
            break;
            
        case SortFieldOptionsStartDate:
            DebugLog(@"LI:PITodo: Adding start date sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            if (invert)
                [fieldName setString:@"ifnull(T.ZSTARTDATEINTERNAL,9999999999)"];
            else
                [fieldName setString:@"T.ZSTARTDATEINTERNAL"];
            [fieldName appendFormat:@"/*Start[%i]*/", invert];
            break;
            
        case SortFieldOptionsTitle:
            DebugLog(@"LI:PITodo: Adding title sort field");
            fieldName = [NSMutableString stringWithCapacity:40];
            [fieldName appendFormat:@"T.ZTITLE/*Title[%i]*/", invert];
            break;
            
        case SortFieldOptionsReorder:
            DebugLog(@"LI:PITodo: Adding reorder sort field");
            fieldName = [NSMutableString stringWithCapacity:40];
            [fieldName appendFormat:@"T.ZSEQUENCE/*Reorder[%i]*/", invert];
            break;
            
        case SortFieldOptionsContext:
            DebugLog(@"LI:PITodo: Adding context sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            [fieldName appendFormat:@"%@/*Context[%i]*/", kFieldNameContextStatus, invert];
            break;
            
        case SortFieldOptionsContextFilter:
            // This sort is a little tricky. The idea is that if a set of context states may
            // be included in the task list then it should be added to the beginning of the
            // sort list. This prevents, say, high priority out of context items blocking low
            // priority in-context items.
            DebugLog(@"LI:PITodo: Adding context sort field");
            fieldName = [NSMutableString stringWithCapacity:100];
            atStart = [prefs boolValue:kPIOptionsShowContextAtStart withDefault:kPIOptionsShowContextAtStartDefault];
            
            // In-context items should always go at the top of the list
            [fieldName appendFormat:@"case when %@=%i then 0", kFieldNameContextStatus, ContextClassificationInContext];
            
            // No context items should be at the top of the list if they aren't always filtered
            [fieldName appendFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationNoContext,
             ([prefs intValue:kPIOptionsHideNoContext withDefault:kPIOptionsHideNoContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            // Out of context items should be at the top of the list if they aren't always filtered
            [fieldName appendFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationOutOfContext,
             ([prefs intValue:kPIOptionsHideOutOfContext withDefault:kPIOptionsHideOutOfContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            [fieldName appendFormat:@" else 2 end/*ContextFilter[%i]*/", invert];
            break;
    }
    
    if (fieldName) {
        if (!ascending)
            [fieldName appendString:@" DESC"];
        [current appendString:fieldName withSeparator:@", "];
    }
}


+ (NSArray*)pi2basicTaskList:(FWApplication*)app database:(FWDatabase*)database prefs:(FWPreferences*)prefs key:(NSString*)key parentKey:(NSString*)parentKey search:(NSString*)search
{
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;

    DebugLog(@"LI:PITodo: Building SQL for prefs %@", prefs);
    
    NSArray* contexts = (NSArray*)[app getAttribute:kPIApplicationContextCurrent];
    NSMutableString* contextList = [NSMutableString stringWithCapacity:100];
    [contextList appendString:@"-1"];
    if (contexts) {
        for (PIContext* context in contexts) {
            [contextList appendFormat:@",%i", context.key];
        }
    }
    

    NSMutableString* allSql = [NSMutableString stringWithCapacity:2000];
    [allSql appendFormat:
     @"select 2, T.ZTITLE, T.ZSTARTDATEINTERNAL, T.ZENDDATEINTERNAL, T.ZISALLDAY, T.ZPRIORITYINTEGER, T.ZFCPRIORITY, T.Z_PK, T.ZFLAGGEDINTEGER, T.ZACTIONINTEGER, T.ZICON, T.ZHASRECURRENCEINTERNAL, P.ZTITLE, T.ZCHILDCOUNTINTERNAL, T.ZPARENTTASK, K.ZTITLE, K.ZICON, K.ZCOLOR,"
     " case when T.ZCONTEXT in (%@) then %i when T.ZCONTEXT is null then %i else %i end %@,"
     " T.ZCHILDCOUNTINTERNAL,"
     " case when T.ZISALLDAY then T.ZENDDATEINTERNAL else T.ZENDDATEINTERNAL+%i end %@"
     " from ZPICALCALENDARITEM T"
     " left join ZPICALCALENDARITEM P on P.Z_PK=T.ZPROJECT"
     " left join ZPICALCALENDAR K on K.Z_PK=T.ZCALENDAR"
     " left join Z_15TAGS M on M.Z_15CALENDARITEMS1=T.Z_PK"
     " where T.Z_ENT=%@ AND T.ZCOMPLETEDDATE is null",
     contextList,
     ContextClassificationInContext,
     ContextClassificationNoContext,
     ContextClassificationOutOfContext,
     kFieldNameContextStatus,
     [[NSTimeZone systemTimeZone] secondsFromGMTForDate:[NSDate date]],
     kFieldNameEndDate,
     [plugin.dbEntityKeys objectForKey:kPIEntityTypeTask]];

    
    // Standard expessions
//    NSString* inbox = @"T.ZCONTEXT is null and T.ZPROJECT is null and T.ZACTIONINTEGER<=0";
    NSString* inbox = @"1=1";

    
    //allSql = [allSql stringByAppendingString:@" and t.title not like '!!%'/*DEBUG*/"];
    
    // Don't return calendars in the ignore list
    if (NSArray* calendars = [prefs arrayValue:kPIOptionsHiddenPICalendars withDefault:nil]) {
        NSMutableString* ignoreCalendars = [NSMutableString stringWithCapacity:calendars.count * 3];
        for (NSString* calendar in calendars) {
            [ignoreCalendars appendString:calendar withSeparator:@","];
        }
        if (ignoreCalendars.length > 0)
            [allSql appendFormat:@" and T.ZCALENDAR not in (%@)/*IgnoreCals*/", ignoreCalendars];
    }

    
    // Only generate filter terms if the option to show all tasks is off    
    if (key) {
        // Only want a single key
        [allSql appendFormat:@" and T.Z_PK=%@/*KeyOnly*/", key];
    }
    else if (parentKey) {
        // Only want children of a single key
        [allSql appendFormat:@" and T.ZPARENTTASK=%@/*ChildrenOnly*/", parentKey];
    }
    else if (search) {
        // Only want tasks that match a pattern   
        search = [search stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        search = [search stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        [allSql appendFormat:@" and T.ZTITLE like '%%%@%%'/*Search*/", search];
    }
    else if (![prefs boolValue:kPIOptionsShowAllTasks withDefault:kPIOptionsShowAllTasksDefault]) {
        int taskFiling = [prefs intValue:kPIOptionsShowFiling withDefault:kPIOptionsShowFilingDefault];
        if (taskFiling == TaskFilingInboxOnly)
            // Only tasks without tags or a context or a project or an action
            [allSql appendFormat:@" and %@/*ShowInboxOnly*/", inbox];
        else if (taskFiling == TaskFilingFiledOnly)
            // Don't allow tasks without tags or a context or a project or an action
            [allSql appendFormat:@" and not(%@)/*ShowFiledOnly*/", inbox];
        
        if ([prefs boolValue:kPIOptionsHideDeferred withDefault:kPIOptionsHideDeferredDefault])
            // Only allow tasks before Delegated
            [allSql appendFormat:@" and T.ZACTIONINTEGER<%d/*HideDeferred*/", PIActionDelegated];
        
        // Get the date maxDays in the future
        NSTimeInterval maxDays = (NSTimeInterval)[prefs intValue:kPIOptionsDayLimit withDefault:kPIOptionsDayLimitDefault];
        NSTimeInterval seconds = maxDays * SECONDS_PER_DAY;
        
        // Get time of midnight today (ie past) in the UTC by building a new date from only the year, month
        // and day components of the current date. PI start dates are always UTC
        NSDate *midnight = FWUTCForLocalDate(FWLocalDateForLocalMidnight());
        //NSDate *midnight = FWLocalDateForLocalMidnight();
        XLog(@"LI:PITodo: Local midnight utc date = %@", midnight);
        
        if ([prefs boolValue:kPIOptionsHideFuture withDefault:kPIOptionsHideFutureDefault])
            // Only allow tasks starting before or at today's midnight or that don't have a start date
            [allSql appendFormat:@" and ifnull(T.ZSTARTDATEINTERNAL,0)<=%i/*HideFuture*/", (int)[midnight timeIntervalSinceReferenceDate]];
        
        // Get future midnight date by adding the number of days we will show (as seconds). Again this will be in UTC
        NSDate* future = [midnight addTimeInterval:seconds];
        //DebugLog(@"LI:PITodo: Cutoff date = %@", future);
        
        // PI stores end times as local rather than UTC so we need to convert the local time to UTC
        // in order to compare with an end date. This will be a hack as srictly we need the number of
        // seconds offset on the given day to account for DST. That's the way that the display functions
        // work.
        NSInteger utcOffset = [[NSTimeZone systemTimeZone] secondsFromGMTForDate:midnight];
        XLog(@"LI:PITodo: UTC offset to local time = %i", utcOffset);
        
        // Only allow items with no duedate or a duedate before the threshold (not at as that would give 1 day too much)
        [allSql appendFormat:@" and ifnull(%i+T.ZENDDATEINTERNAL,0)<%i/*MaxLimit-%i*/", utcOffset, (int)[future timeIntervalSinceReferenceDate], (int)maxDays];
        
        
        // Only return tags in the show list if we have one
        if (NSArray* tags = [prefs arrayValue:kPIOptionsTagsFilters withDefault:nil]) {
            NSMutableString* selectTags = [NSMutableString stringWithCapacity:tags.count * 3];
            for (NSString* tag in tags) {
                [selectTags appendString:tag withSeparator:@","];
            }
            if (selectTags.length > 0)
                [allSql appendFormat:@" and M.Z_37TAGS in (%@)/*SelectTags*/", selectTags];
        }
        
        
        int showParents = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault];
        if (showParents == HideParentIfChildren)
            // Only allow items that don't have children
            [allSql appendString:@" and T.ZCHILDCOUNTINTERNAL=0/*HideWithChild*/"];
        
        if ([prefs boolValue:kPIOptionsHideUndated withDefault:kPIOptionsHideUndatedDefault])
            // Only allow items that don't have a duedate
            [allSql appendString:@" and T.ZENDDATEINTERNAL is not null/*HideUndated*/"];
        
        int actionType = [prefs intValue:kPIOptionsShowOnlyAction withDefault:kPIOptionsShowOnlyActionDefault];
        if (actionType >= 0)
            // Only allow items with the correct action
            [allSql appendFormat:@" and T.ZACTIONINTEGER=%i/*OnlyAction*/", actionType];
        
        if ([prefs boolValue:kPIOptionsStarredOnly withDefault:kPIOptionsStarredOnlyDefault])
            // Only allow items that have been flagged
            [allSql appendString:@" and T.ZFLAGGEDINTEGER=1/*StarredOnly*/"];
    }
    else
        [allSql appendString:@" /*ShowAllTasks*/"];
    
    [allSql appendString:@" group by T.Z_PK"];
    
    if (parentKey) {
        // Add sorting terms
        NSMutableString* sortExpr = [NSMutableString stringWithCapacity:200];
        
        [PITaskIntegration pi2addSortField:sortExpr field:SortFieldOptionsReorder ascending:YES invert:NO prefs:prefs];
        [PITaskIntegration pi2addSortField:sortExpr field:SortFieldOptionsDueDate ascending:YES invert:NO prefs:prefs];
        [allSql appendString:@" order by "];
        [allSql appendString:sortExpr];
    }
    else if (!key) {
        // Add sorting terms
        NSMutableString* sortExpr = [NSMutableString stringWithCapacity:200];
        
        int sort1 = [prefs intValue:kPIOptionsSortField1 withDefault:kPIOptionsSortField1Default];
        int sort2 = [prefs intValue:kPIOptionsSortField2 withDefault:kPIOptionsSortField2Default];
        
        // If we are using context filtering and neither of the sort settings is context then add a context sort
        if ((((PITodoStatus*)app.info).isAutoContextOn || ((PITodoStatus*)app.info).isFixedContextOn) && (sort1 != SortFieldOptionsContext) && (sort2 != SortFieldOptionsContext))
            [PITaskIntegration pi2addSortField:sortExpr field:SortFieldOptionsContextFilter ascending:YES invert:NO prefs:prefs];
        
        BOOL sortAsc1 = [prefs boolValue:kPIOptionsSortField1Asc withDefault:kPIOptionsSortField1AscDefault];
        int noneLast1 = [prefs intValue:kPIOptionsSortField1Order withDefault:kPIOptionsSortField1OrderDefault];
        [PITaskIntegration pi2addSortField:sortExpr field:sort1 ascending:sortAsc1 invert:(noneLast1 == SortFieldNoneOrderLast) prefs:prefs];
        
        BOOL sortAsc2 = [prefs boolValue:kPIOptionsSortField2Asc withDefault:kPIOptionsSortField2AscDefault];
        int noneLast2 = [prefs intValue:kPIOptionsSortField2Order withDefault:kPIOptionsSortField2OrderDefault];
        if (sort1 != sort2)
            [PITaskIntegration pi2addSortField:sortExpr field:sort2 ascending:sortAsc2 invert:(noneLast2 == SortFieldNoneOrderLast) prefs:prefs];
        
        if (sortExpr.length > 0) {
            [allSql appendString:@" order by "];
            [allSql appendString:sortExpr];
        }
        
        // Restrict the number of items returned to the requested number		
        int queryLimit = search ? kMaxSearchItems : [prefs intValue:kPIOptionsMaxTasks withDefault:kPIOptionsMaxTasksDefault];
        [allSql appendFormat:@" limit %i", queryLimit];
    }
    
    XLog(@"LI:PITask: SQL = %@", allSql);
    
    PITaskIntegration* exec = [[PITaskIntegration alloc] init];
    NSArray* todos = FWDBGetRecordsFromConnection(allSql, kSQLBasicTaskList, nil, database, app, exec);
    [exec release];
    
    if (todos)
        return todos;
    else
        return [NSArray array];
}


+ (void)addSortField:(NSMutableString*)current field:(int)field ascending:(BOOL)ascending invert:(BOOL)invert prefs:(FWPreferences*)prefs
{
    DebugLog(@"LI:PITodo: Adding SQL sort field, current=%@; field=%i; ascend=%i; invert=%i", current, field, ascending, invert);        

    if (field == SortFieldOptionsNone)
        return;
    
    NSMutableString* fieldName;
    BOOL atStart;
    switch (field) {
        case SortFieldOptionsImportance:
            DebugLog(@"LI:PITodo: Adding importance sort field");
            fieldName = [NSMutableString stringWithCapacity:80];
            if (invert)
                [fieldName setString:@"case t.priority when 0 then 99 else t.priority end"];
            else
                [fieldName setString:@"t.priority"];

            [fieldName appendFormat:@"/*Importance[%i]*/", invert];
            break;
            
        case SortFieldOptionsPriority:
            // Sort by initial letter if present or '-/~' if starts with a number - so numbers precede or follow letters
            // depending upon the invert flag state. If no value at all then use '~~' to ensure no fc value is always
            // at the end of the list.
            DebugLog(@"LI:PITodo: Adding priority sort field");
            fieldName = [NSMutableString stringWithCapacity:240];
            [fieldName appendFormat:
             @"case when ifnull(t.fcpriority,'')='' then '~~'"
             " when abs(substr(t.fcpriority,1,1))>0 then '%@'"
             " else substr(t.fcpriority,1,1) end", invert ? @"~" : @"-"];
            if (!ascending)
                [fieldName appendString:@" DESC"];

            [fieldName appendString:
             @", case when abs(substr(t.fcpriority,1,1))=0 then abs(substr(t.fcpriority,2))"
             " else abs(t.fcpriority) end"];

            [fieldName appendFormat:@"/*Priority[%i]*/", invert];
            break;
            
        case SortFieldOptionsAction:
            DebugLog(@"LI:PITodo: Adding action sort field");
            fieldName = [NSMutableString stringWithCapacity:80];
            if (invert)
                [fieldName setString:@"case t.action when 0 then 99 else t.action end"];
            else
                [fieldName setString:@"t.action"];

            [fieldName appendFormat:@"/*Action[%i]*/", invert];
            break;
            
        case SortFieldOptionsDueDate:
            // PI stores end times as local rather than UTC so we need to convert the local time to UTC
            // in order to compare with an end date. This will be a hack as srictly we need the number of
            // seconds offset on the given day to account for DST. That's the way that the display functions
            // work.
            DebugLog(@"LI:PITodo: Adding due date sort field");
            fieldName = [NSMutableString stringWithCapacity:90];
            if (invert)
                [fieldName appendFormat:@"coalesce(t.duedate,t.duedatetime+ %i,9999999999)",
                 [[NSTimeZone systemTimeZone] secondsFromGMTForDate:[NSDate date]]];
            else
                [fieldName appendFormat:@"ifnull(t.duedate,t.duedatetime+ %i)",
                 [[NSTimeZone systemTimeZone] secondsFromGMTForDate:[NSDate date]]];

            [fieldName appendFormat:@"/*Due[%i]*/", invert];
            break;
            
        case SortFieldOptionsStartDate:
            DebugLog(@"LI:PITodo: Adding start date sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            if (invert)
                [fieldName setString:@"ifnull(t.startdate,9999999999)"];
            else
                [fieldName setString:@"t.startdate"];
            [fieldName appendFormat:@"/*Start[%i]*/", invert];
            break;
            
        case SortFieldOptionsTitle:
            DebugLog(@"LI:PITodo: Adding title sort field");
            fieldName = [NSMutableString stringWithCapacity:40];
            [fieldName appendFormat:@"t.title/*Title[%i]*/", invert];
            break;
            
        case SortFieldOptionsReorder:
            DebugLog(@"LI:PITodo: Adding reorder sort field");
            fieldName = [NSMutableString stringWithCapacity:40];
            [fieldName appendFormat:@"t.sortfield/*Reorder[%i]*/", invert];
            break;
            
        case SortFieldOptionsContext:
            DebugLog(@"LI:PITodo: Adding context sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            [fieldName appendFormat:@"%@/*Context[%i]*/", kFieldNameContextStatus, invert];
            break;
            
        case SortFieldOptionsContextFilter:
            // This sort is a little tricky. The idea is that if a set of context states may
            // be included in the task list then it should be added to the beginning of the
            // sort list. This prevents, say, high priority out of context items blocking low
            // priority in-context items.
            DebugLog(@"LI:PITodo: Adding context sort field");
            fieldName = [NSMutableString stringWithCapacity:100];
            atStart = [prefs boolValue:kPIOptionsShowContextAtStart withDefault:kPIOptionsShowContextAtStartDefault];
            
            // In-context items should always go at the top of the list
            [fieldName appendFormat:@"case when %@=%i then 0", kFieldNameContextStatus, ContextClassificationInContext];
            
            // No context items should be at the top of the list if they aren't always filtered
            [fieldName appendFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationNoContext,
                         ([prefs intValue:kPIOptionsHideNoContext withDefault:kPIOptionsHideNoContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            // Out of context items should be at the top of the list if they aren't always filtered
            [fieldName appendFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationOutOfContext,
                         ([prefs intValue:kPIOptionsHideOutOfContext withDefault:kPIOptionsHideOutOfContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            [fieldName appendFormat:@" else 2 end/*ContextFilter[%i]*/", invert];
            break;
    }
    
    if (fieldName) {
        if (!ascending)
            [fieldName appendString:@" DESC"];
        [current appendString:fieldName withSeparator:@", "];
    }
}

+ (NSArray*)basicTaskList:(FWApplication*)app database:(FWDatabase*)database prefs:(FWPreferences*)prefs key:(NSString*)key parentKey:(NSString*)parentKey search:(NSString*)search
{
    if (((PIPlugin*)app.fwPlugin).isPocketInformant2)
        return [PITaskIntegration pi2basicTaskList:app database:database prefs:prefs key:key parentKey:parentKey search:search];

    // If PI not found then do nothing
    if (!((PIPlugin*)app.fwPlugin).isPocketInformantPresent)
        return [NSArray array];
    
    DebugLog(@"LI:PITodo: Building SQL for prefs %@", prefs);

    NSArray* contexts = (NSArray*)[app getAttribute:kPIApplicationContextCurrent];
    NSMutableString* contextList = [NSMutableString stringWithCapacity:100];
    [contextList appendString:@"-1"];
    if (contexts) {
        for (PIContext* context in contexts) {
            [contextList appendFormat:@",%i", context.key];
        }
    }

    // Get the standard information we'll need
    BOOL useFilters = (key == nil) && (parentKey == nil);
    BOOL calcParents = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault] == HideParentIfChildren;
    if (!useFilters)
        calcParents = YES;
    
    NSMutableString* allSql = [NSMutableString stringWithCapacity:2000];
    [allSql appendFormat:@"select 1, t.title, t.startdate, t.duedate, t.duedatetime, t.priority, t.fcpriority, t.id, t.flagged, t.action, t.icon, t.recurrenceid, p.title, t.isparent, t.parenttaskid, k.title, k.icon, 1,"
                        " case when t.contextid in (%@) then %i when t.contextid=0 then %i else %i end %@,"
                        " %@ %@"
                        " from task t"
                        " left join calendar k on t.calendarid = k.id"
                        " left join context c on t.contextid = c.id"
                        " left join task p on t.projectid = p.id"
                        " left join itemtagmap m on m.itemid = t.id and m.itemtable='task'"
                        " where t.tasktype <> 1 and t.completeddate is null and (t.calendarid=-2 or t.calendarid>=0)",
                        contextList,
                        ContextClassificationInContext,
                        ContextClassificationNoContext,
                        ContextClassificationOutOfContext,
                        kFieldNameContextStatus,
                        calcParents ? @"(select count(*) from task where parenttaskid = t.id)" : @"-1",
                        kFieldNameChildCount];
    
    // Standard expessions
    NSString* inbox = @"t.contextid<=0 and t.projectid<=0 and t.action<=0"; // and g.title is null"; -- This needs fixing for 2.0
    
    
    //allSql = [allSql stringByAppendingString:@" and t.title not like '!!%'/*DEBUG*/"];
    
    // Don't return calendars in the ignore list
    if (NSArray* calendars = [prefs arrayValue:kPIOptionsHiddenPICalendars withDefault:nil]) {
        NSMutableString* ignoreCalendars = [NSMutableString stringWithCapacity:calendars.count * 3];
        for (NSString* calendar in calendars) {
            [ignoreCalendars appendString:calendar withSeparator:@","];
        }
        if (ignoreCalendars.length > 0)
            [allSql appendFormat:@" and t.calendarid not in (%@)/*IgnoreCals*/", ignoreCalendars];
    }
    
    // Only generate filter terms if the option to show all tasks is off    
    if (key) {
        // Only want a single key
        [allSql appendFormat:@" and t.id=%@/*KeyOnly*/", key];
    }
    else if (parentKey) {
        // Only want children of a single key
        [allSql appendFormat:@" and t.parenttaskid=%@/*ChildrenOnly*/", parentKey];
    }
    else if (search) {
        // Only want tasks that match a pattern   
        search = [search stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        search = [search stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        [allSql appendFormat:@" and t.title like '%%%@%%'/*Search*/", search];
    }
    else if (![prefs boolValue:kPIOptionsShowAllTasks withDefault:kPIOptionsShowAllTasksDefault]) {
        int taskFiling = [prefs intValue:kPIOptionsShowFiling withDefault:kPIOptionsShowFilingDefault];
        if (taskFiling == TaskFilingInboxOnly)
            // Only tasks without tags or a context or a project or an action
            [allSql appendFormat:@" and %@/*ShowInboxOnly*/", inbox];
        else if (taskFiling == TaskFilingFiledOnly)
            // Don't allow tasks without tags or a context or a project or an action
            [allSql appendFormat:@" and not(%@)/*ShowFiledOnly*/", inbox];
        
        if ([prefs boolValue:kPIOptionsHideDeferred withDefault:kPIOptionsHideDeferredDefault])
            // Only allow tasks before Delegated
            [allSql appendFormat:@" and t.action<%d/*HideDeferred*/", PIActionDelegated];
        
        // Get the date maxDays in the future
        NSTimeInterval maxDays = (NSTimeInterval)[prefs intValue:kPIOptionsDayLimit withDefault:kPIOptionsDayLimitDefault];
        NSTimeInterval seconds = maxDays * SECONDS_PER_DAY;
        
        // Get time of midnight today (ie past) in the UTC by building a new date from only the year, month
        // and day components of the current date. PI start dates are always UTC
        NSDate *midnight = FWUTCForLocalDate(FWLocalDateForLocalMidnight());
        //NSDate *midnight = FWLocalDateForLocalMidnight();
        XLog(@"LI:PITodo: Local midnight utc date = %@", midnight);
        
        if ([prefs boolValue:kPIOptionsHideFuture withDefault:kPIOptionsHideFutureDefault])
            // Only allow tasks starting before or at today's midnight or that don't have a start date
            [allSql appendFormat:@" and ifnull(t.startdate,0)<=%i/*HideFuture*/", (int)[midnight timeIntervalSinceReferenceDate]];
        
        // Get future midnight date by adding the number of days we will show (as seconds). Again this will be in UTC
        NSDate* future = [midnight addTimeInterval:seconds];
        //DebugLog(@"LI:PITodo: Cutoff date = %@", future);
        
        // PI stores end times as local rather than UTC so we need to convert the local time to UTC
        // in order to compare with an end date. This will be a hack as srictly we need the number of
        // seconds offset on the given day to account for DST. That's the way that the display functions
        // work.
        NSInteger utcOffset = [[NSTimeZone systemTimeZone] secondsFromGMTForDate:midnight];
        XLog(@"LI:PITodo: UTC offset to local time = %i", utcOffset);
        
        // Only allow items with no duedate or a duedate before the threshold (not at as that would give 1 day too much)
        [allSql appendFormat:@" and coalesce(t.duedate,%i+t.duedatetime,0)<%i/*MaxLimit-%i*/", utcOffset, (int)[future timeIntervalSinceReferenceDate], (int)maxDays];

        
        // Only return tags in the show list if we have one
        if (NSArray* tags = [prefs arrayValue:kPIOptionsTagsFilters withDefault:nil]) {
            NSMutableString* selectTags = [NSMutableString stringWithCapacity:tags.count * 3];
            for (NSString* tag in tags) {
                [selectTags appendString:tag withSeparator:@","];
            }
            if (selectTags.length > 0)
                [allSql appendFormat:@" and m.tagid in (%@)/*SelectTags*/", selectTags];
        }
        
        
        int showParents = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault];
        if (showParents == HideParentIfChildren)
            // Only allow items that don't have children
            [allSql appendFormat:@" and %@<=0/*HideWithChild*/", kFieldNameChildCount];
        
        if ([prefs boolValue:kPIOptionsHideUndated withDefault:kPIOptionsHideUndatedDefault])
            // Only allow items that don't have a duedate or duedatetime
            [allSql appendString:@" and ifnull(t.duedate,t.duedatetime) is not null/*HideUndated*/"];
        
        int actionType = [prefs intValue:kPIOptionsShowOnlyAction withDefault:kPIOptionsShowOnlyActionDefault];
        if (actionType >= 0)
            // Only allow items with the correct action
            [allSql appendFormat:@" and t.action=%i/*OnlyAction*/", actionType];
        
        if ([prefs boolValue:kPIOptionsStarredOnly withDefault:kPIOptionsStarredOnlyDefault])
            // Only allow items that have been flagged
            [allSql appendString:@" and t.flagged=1/*StarredOnly*/"];
    }
    else
        [allSql appendString:@" /*ShowAllTasks*/"];
    
    [allSql appendString:@" group by t.id"];

    if (parentKey) {
        // Add sorting terms
        NSMutableString* sortExpr = [NSMutableString stringWithCapacity:200];

        [PITaskIntegration addSortField:sortExpr field:SortFieldOptionsReorder ascending:YES invert:NO prefs:prefs];
        [PITaskIntegration addSortField:sortExpr field:SortFieldOptionsDueDate ascending:YES invert:NO prefs:prefs];
        [allSql appendString:@" order by "];
        [allSql appendString:sortExpr];
    }
    else if (!key) {
        // Add sorting terms
        NSMutableString* sortExpr = [NSMutableString stringWithCapacity:200];
        
        int sort1 = [prefs intValue:kPIOptionsSortField1 withDefault:kPIOptionsSortField1Default];
        int sort2 = [prefs intValue:kPIOptionsSortField2 withDefault:kPIOptionsSortField2Default];

        // If we are using context filtering and neither of the sort settings is context then add a context sort
        if ((((PITodoStatus*)app.info).isAutoContextOn || ((PITodoStatus*)app.info).isFixedContextOn) && (sort1 != SortFieldOptionsContext) && (sort2 != SortFieldOptionsContext))
            [PITaskIntegration addSortField:sortExpr field:SortFieldOptionsContextFilter ascending:YES invert:NO prefs:prefs];
        
        BOOL sortAsc1 = [prefs boolValue:kPIOptionsSortField1Asc withDefault:kPIOptionsSortField1AscDefault];
        int noneLast1 = [prefs intValue:kPIOptionsSortField1Order withDefault:kPIOptionsSortField1OrderDefault];
        [PITaskIntegration addSortField:sortExpr field:sort1 ascending:sortAsc1 invert:(noneLast1 == SortFieldNoneOrderLast) prefs:prefs];
        
        BOOL sortAsc2 = [prefs boolValue:kPIOptionsSortField2Asc withDefault:kPIOptionsSortField2AscDefault];
        int noneLast2 = [prefs intValue:kPIOptionsSortField2Order withDefault:kPIOptionsSortField2OrderDefault];
        if (sort1 != sort2)
            [PITaskIntegration addSortField:sortExpr field:sort2 ascending:sortAsc2 invert:(noneLast2 == SortFieldNoneOrderLast) prefs:prefs];
        
        if (sortExpr.length > 0) {
            [allSql appendString:@" order by "];
            [allSql appendString:sortExpr];
        }
        
        // Restrict the number of items returned to the requested number		
        int queryLimit = search ? kMaxSearchItems : [prefs intValue:kPIOptionsMaxTasks withDefault:kPIOptionsMaxTasksDefault];
        [allSql appendFormat:@" limit %i", queryLimit];
    }
    
    PITaskIntegration* exec = [[PITaskIntegration alloc] init];
    NSArray* todos = FWDBGetRecordsFromConnection(allSql, kSQLBasicTaskList, nil, database, app, exec);
    [exec release];

    if (todos)
        return todos;
    else
        return [NSArray array];
}

- (NSObject*)sqlBasicTaskList:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application
{
    // Read fields from SQL record
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSString* text = FWDBReadColumnString(sqlStatement, 1);
    double cStart = sqlite3_column_double(sqlStatement, 2);
    double cDue = sqlite3_column_double(sqlStatement, 3);
    double cDueTime = sqlite3_column_double(sqlStatement, 4);
    NSInteger importance = sqlite3_column_int(sqlStatement, 5);
    NSString* priority = FWDBReadColumnString(sqlStatement, 6);
    NSInteger primaryKey = sqlite3_column_int(sqlStatement, 7);
    NSInteger flagged = sqlite3_column_int(sqlStatement, 8);
    NSInteger action = sqlite3_column_int(sqlStatement, 9);
    NSString* icon = FWDBReadColumnString(sqlStatement, 10);
    NSInteger recurrance = sqlite3_column_int(sqlStatement, 11);
    NSString* project = FWDBReadColumnString(sqlStatement, 12);
    NSInteger parent = sqlite3_column_int(sqlStatement, 13);
    NSInteger child = sqlite3_column_int(sqlStatement, 14);

    NSString* calendarTitle = FWDBReadColumnString(sqlStatement, 15);
    NSString* calendarIcon = FWDBReadColumnString(sqlStatement, 16);
        
    NSInteger context = sqlite3_column_int(sqlStatement, 18);
    NSInteger children = sqlite3_column_int(sqlStatement, 19);
    
    UIColor* calendarColor = [UIColor clearColor];
    if (version == 2)
        calendarColor = FWDBReadColumnBlobWithDefault(sqlStatement, 17, calendarColor);

    // If PI1 then is an all-day item if no due time. For PI 2 return a 0/1 value in the same field
    BOOL allDay = version == 1 ? (cDueTime == 0) : (cDueTime != 0);
        
    //    BOOL detailsPresent = ((cStart != 0) || (cDue != 0) || (cDueTime != 0) || (aPriority.length > 0) || (aProject.length > 0));
        
    //    // Create a dictionary containing the fields we want to display
    //    NSDictionary *todoDict = [NSMutableDictionary dictionaryWithObjectsAndKeys:
    //                              aText, kPITodoListAccessFieldName,
    //                              FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:cStart]), kPITodoListAccessFieldStartDate,
    //                              FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:cDue]), kPITodoListAccessFieldDueDate,
    //                              [NSDate nullableDateWithTimeIntervalSinceReferenceDate:cDueTime], kPITodoListAccessFieldDueTime,
    //                              [NSNumber numberWithInt:importance], kPITodoListAccessFieldImportance, 
    //                              aPriority, kPITodoListAccessFieldPriority,
    //                              [NSNumber numberWithInt:flagged], kPITodoListAccessFieldFlagged, 
    //                              aIcon, kPITodoListAccessFieldIcon,
    //                              aProject, kPITodoListAccessFieldProject,
    //                              [NSNumber numberWithInt:primaryKey], kPITodoListAccessFieldKey, 
    //                              [NSNumber numberWithInt:action], kPITodoListAccessFieldAction,
    //                              [NSNumber numberWithBool:detailsPresent], kPITodoListAccessFieldHasDetails,
    //                              [NSNumber numberWithInt:recurrance], kPITodoListAccessFieldIsRecurring,
    //                              [NSNumber numberWithInt:context], kPITodoListAccessFieldContext,
    //                              [NSNumber numberWithInt:parent | (children > 0)], kPITodoListAccessFieldIsParent,
    //                              [NSNumber numberWithInt:child], kPITodoListAccessFieldIsChild,
    //                              [NSNumber numberWithInt:children], kPITodoListAccessFieldChildren,
    //                              nil];
        
    PITask* task = [PITask piTaskWithKey:[[NSNumber numberWithInt:primaryKey] stringValue]
                                   title:text
                                    icon:icon
                               startDate:FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:cStart])
                            startDateRaw:cStart
                                 dueDate:allDay ? FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:cDue]) : [NSDate nullableDateWithTimeIntervalSinceReferenceDate:cDue]
                              dueDateRaw:cDue
                                  allDay:allDay
                                calendar:calendarTitle
                            calendarIcon:calendarIcon
                           calendarColor:calendarColor
                               parentKey:[[NSNumber numberWithInt:child] stringValue]];

    task.contextClassification = (PIClassificationType)context;
    task.flagged = flagged;
    task.project = project;
    task.priority = priority;
    task.importance = (PIImportanceType)importance;
    task.action = (PIActionType)action;
    task.repeating = recurrance != 0;
    task.child = child != 0;
    task.parent = parent || (children > 0);
    
    return task;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Adding child data to an existing task list

+ (void)addChildDataToTaskList:(FWApplication*)app database:(FWDatabase*)database parents:(NSArray*)parents prefs:(FWPreferences*)prefs
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return;

    // If we have PI2 then we already have the child counts
    if (plugin.isPocketInformant2)
        return;

    
    NSMutableString* keys = [NSMutableString stringWithCapacity:50];
    for (PITask* item in parents) {
        if (!item.isChild && !item.isParent) {
            [keys appendString:item.key withSeparator:@","];
        }
    }
    
    NSString* sql = [NSString stringWithFormat:
                     @"select 1, parenttaskid, count(*) from task where parenttaskid in (%@) group by parenttaskid",
                     keys];
    
    PITaskIntegration* exec = [[PITaskIntegration alloc] init];
    NSArray* childCounts = FWDBGetRecordsFromConnection(sql, kSQLAddChildData, nil, database, app, exec);
    [exec release];
    
    for (NSArray* childCount in childCounts) {
        NSNumber* children = [childCount objectAtIndex:1];
        if ([children intValue] > 0) {
            NSString* key = [childCount objectAtIndex:0];
            PITask* task = [parents piObjectForKey:key];
            if (task)
                task.parent = YES;
        }
    }
}

- (NSObject*)sqlAddChildDataToTaskList:(sqlite3_stmt*)sqlStatement application:application
{
    int version = sqlite3_column_int(sqlStatement, 0);
    int primaryKey = sqlite3_column_int(sqlStatement, 1);
    int children = sqlite3_column_int(sqlStatement, 2);

    return [NSArray arrayWithObjects:
            [[NSNumber numberWithInt:primaryKey] stringValue], 
            [NSNumber numberWithInt:children],
            nil];
}


// ******************************************************************************************
#pragma mark -
#pragma mark Augmenting a task - returns a new task

+ (PITask*)augmentedBasicTask:(PITask*)task application:(FWApplication*)app
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return task;

    // Get a new copy of the task
    PITask* newTask = [[task copy] autorelease];
    newTask.augmented = YES;
    
    PITaskIntegration* exec = [[PITaskIntegration alloc] init];
    FWDatabase* database = nil;
    
    @try {
        database = FWDBConnectDatabase(app.databasePath, app);
        if (!database)
            return newTask;

        NSString* sql;
        if (plugin.isPocketInformant2)
            sql = [NSString stringWithFormat:
                   @"select 2, N.ZNOTES, T.ZPERCENTCOMPLETE, C.ZTITLE, P.ZTITLE, T.ZCHILDCOUNTINTERNAL,"
                   " group_concat(M.Z_37TAGS, ','),"
                   " R.ZDAYSOFTHEMONTH, R.ZDAYSOFTHEWEEK, R.ZENDDATE, R.ZFIRSTDAYOFTHEWEEK, R.ZMONTHSOFTHEYEAR, R.ZOCCURRENCECOUNT, R.ZRECURRENCEINTERVAL, R.ZRECURRENCETYPEINTEGER, T.ZRECURRENCETYPEINTEGER"
                   " from ZPICALCALENDARITEM T"
                   " left join ZPICALCALENDARITEM A on T.ZPARENTTASK=A.Z_PK"
                   " left join ZPICONTEXT C on T.ZCONTEXT=C.Z_PK"
                   " left join ZPICALCALENDARITEM P on T.ZPROJECT=P.Z_PK"
                   " left join Z_15TAGS M on M.Z_15CALENDARITEMS1=T.Z_PK"
                   " left join ZPICALRECURRENCERULE R on R.ZPARENTITEM=T.Z_PK"
                   " left join ZNOTESCONTAINER N on N.ZCALENDARITEM=T.Z_PK"
                   " where T.Z_PK = %@"
                   " group by T.Z_PK",
                   task.key];
        else
            sql = [NSString stringWithFormat:
                   @"select 1, t.notes, t.percentcomplete, c.name, p.title,"
                   " (select count(*) from task where parenttaskid = t.id),"
                   " group_concat(m.tagid, ',')"
                   " from task t"
                   " left join task a on t.parenttaskid = a.id"
                   " left join context c on t.contextid = c.id"
                   " left join task p on t.projectid = p.id"
                   " left join itemtagmap m on m.itemid = t.id and m.itemtable='task'"
                   " where t.id = %@"
                   " group by t.id",
                   task.key];
        
        NSArray* extras = FWDBGetRecordsFromConnection(sql, kSQLAugmentedTask, nil, database, app, exec);
        
        extras = [extras objectAtIndex:0];
        newTask.notes = [extras objectAtIndex:0];
        if (newTask.notes.length == 0)
            newTask.notes = nil;

        newTask.percentComplete = [[extras objectAtIndex:1] intValue];
        newTask.context = [extras objectAtIndex:2];
        newTask.project = [extras objectAtIndex:3];

        NSArray* tags = [extras objectAtIndex:5];
        if (tags.count > 0) {
            NSMutableArray* newTags = [NSMutableArray arrayWithCapacity:tags.count];
            NSArray* allTags = [PIIntegration arrayOfTags:app keys:nil database:database];
            for (NSString* key in tags) {
                if (PIContextObject* tag = [allTags piContextObjectForKey:key.intValue])
                    [newTags addObject:tag];
            }
            if (newTags.count > 0) {
                [newTags sortUsingFunction:compareContextObjectNames context:nil];   
                newTask.tags = newTags;
            }
        }
        
        if (task.isRepeating && plugin.isPocketInformant2) {
            NSArray* daysOfMonth = [extras objectAtIndex:6];
            NSMutableArray* daysOfWeek = [NSMutableArray arrayWithArray:[extras objectAtIndex:7]];
            NSInteger firstDayOfWeek = [[extras objectAtIndex:8] intValue];
            NSArray* monthsOfYear = [extras objectAtIndex:9];
            NSDate* endDate = [extras objectAtIndex:10];
            NSInteger occurances = [[extras objectAtIndex:11] intValue];
            NSInteger interval = [[extras objectAtIndex:12] intValue];
            NSInteger type = [[extras objectAtIndex:13] intValue];
            NSInteger taskType = [[extras objectAtIndex:14] intValue];
            
            newTask.recurrance = [PIIntegration recurrenceWithFrequency:type
                                                               interval:interval
                                                          daysOfTheWeek:daysOfWeek
                                                         daysOfTheMonth:daysOfMonth
                                                        monthsOfTheYear:monthsOfYear
                                                             occurances:occurances
                                                                endDate:endDate
                                                               fromDate:(PIRecurranceFromDateType)taskType];
            
#ifdef DEBUG
            if (plugin.isDebugMode)
                newTask.notes = [NSString stringWithFormat:
                                 @"days of month = '%@'\n"
                                 "days of week = '%@'\n"
                                 "first day of week = %d\n"
                                 "months of year = '%@'\n"
                                 "end date = '%@'\n"
                                 "occurances = '%d'\n"
                                 "interval = '%d'\n"
                                 "type = '%d'\n"
                                 "taskType = '%d'\n"
                                 "------------------------\n"
                                 "%@",
                                 daysOfMonth,
                                 daysOfWeek,
                                 firstDayOfWeek,
                                 monthsOfYear,
                                 endDate,
                                 occurances,
                                 interval,
                                 type,
                                 taskType,
                                 newTask.notes];
#endif
        }

    }
    @finally {
        [exec release];
        FWDBDisconnectDatabase(database);
    }

    return newTask;
}

- (NSNumber*)sqlAugmentedBasicTask:(sqlite3_stmt*)sqlStatement application:application
{
    // Read fields from SQL record
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSString* notes = FWDBReadColumnString(sqlStatement, 1);
    double percentage = sqlite3_column_double(sqlStatement, 2);
    NSString* context = FWDBReadColumnString(sqlStatement, 3);
    NSString* project = FWDBReadColumnString(sqlStatement, 4);
    NSInteger childCount = sqlite3_column_int(sqlStatement, 5);
    NSString* tags = FWDBReadColumnString(sqlStatement, 6);
    
    if (version == 1) {
        return [NSArray arrayWithObjects:
                notes,
                [NSNumber numberWithInt:percentage],
                context,
                project,
                [NSNumber numberWithInt:childCount],
                [tags componentsSeparatedByString:@","],
                nil];
    }
    else {
        NSArray* daysOfMonth = FWDBReadColumnBlobToArray(sqlStatement, 7);
        NSArray* daysOfWeek = FWDBReadColumnBlobToArray(sqlStatement, 8);
        double endDate = sqlite3_column_double(sqlStatement, 9);
        NSInteger cFirstDayOfWeek = sqlite3_column_int(sqlStatement, 10);
        NSArray* monthsOfYear = FWDBReadColumnBlobToArray(sqlStatement, 11);
        NSInteger occurances = sqlite3_column_int(sqlStatement, 12);
        NSInteger interval = sqlite3_column_int(sqlStatement, 13);
        NSInteger type = sqlite3_column_int(sqlStatement, 14);
        NSInteger taskType = sqlite3_column_int(sqlStatement, 15);
        
        return [NSArray arrayWithObjects:
                notes,
                [NSNumber numberWithInt:percentage],
                context,
                project,
                [NSNumber numberWithInt:childCount],
                [tags componentsSeparatedByString:@","],
                daysOfMonth,
                daysOfWeek,
                [NSNumber numberWithInt:cFirstDayOfWeek],
                monthsOfYear,
                [NSDate dateWithTimeIntervalSinceReferenceDate:endDate],
                [NSNumber numberWithInt:occurances],
                [NSNumber numberWithInt:interval],
                [NSNumber numberWithInt:type],
                [NSNumber numberWithInt:taskType],
                notes,
                nil];
    }
}


// ******************************************************************************************
#pragma mark -
#pragma mark Fetching a list of contexts

+ (NSArray*)arrayOfContexts:(FWApplication *)application earliest:(NSDate*)earliest
{  
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)application.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return [NSArray array];

    NSString *sql;
    if (plugin.isPocketInformant2)
        sql = [NSString stringWithFormat:
               @"select 2, C.Z_PK, C.ZTITLE, C.ZICON, C.ZLOCATIONNOTIFICATIONRADIUS, count(T.Z_PK)"
               " from ZPICONTEXT C"
               " left join ZPICALCALENDARITEM T on C.Z_PK=T.ZCONTEXT and T.ZCOMPLETEDDATE is null and T.Z_ENT=%@"
               "%@"
               " group by C.Z_PK"
               " order by C.ZTITLE",
               [plugin.dbEntityKeys objectForKey:kPIEntityTypeTask],
               earliest ? [NSString stringWithFormat:@" and ifnull(T.ZSTARTDATEINTERNAL,0)<=%i", (int)[earliest timeIntervalSinceReferenceDate]] : @""];
    else
        sql = [NSString stringWithFormat:
               @"select 1, c.id, c.name, c.icon, c.notifyRadius, count(t.id)"
               " from context c"
               " left join task t on c.id = t.contextid and t.completeddate is null and t.tasktype <> 1 and (t.calendarID >= 0 or t.calendarid = -2)"
               "%@"
               " group by c.id"
               " order by c.name",
               earliest ? [NSString stringWithFormat:@" and ifnull(t.startdate,0)<=%i", (int)[earliest timeIntervalSinceReferenceDate]] : @""];
    
    PITaskIntegration* exec = [[PITaskIntegration alloc] init];
    NSArray* items = FWDBGetRecords(sql, kSQLArrayOfContexts, nil, application.databasePath, application, exec);
    [exec release];

    if (items)
        return items;
    else
        return [NSArray array];
}

- (NSObject*)sqlArrayOfContexts:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSInteger key = sqlite3_column_int(sqlStatement, 1);
    NSString* title = FWDBReadColumnString(sqlStatement, 2);
    NSString* icon = FWDBReadColumnString(sqlStatement, 3);
    double radius = sqlite3_column_double(sqlStatement, 4);
    NSInteger count = sqlite3_column_int(sqlStatement, 5);
    
    if ([icon length] > 0)
        title = [title stringByAppendingFormat:@" %@", icon];
    
    PIContext* context = [PIContext contextForKey:key title:title];
    context.count = count;
    context.locationAware = radius > 0;

    return context;
}


// ******************************************************************************************
#pragma mark -
#pragma mark URL to create a new task

+ (NSString*)newTaskURL:(NSString*)title importance:(enum PIImportanceType)importance application:(FWApplication*)app
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return [NSArray array];

    NSString* encodedTitle = FWXMLEncodeString(title);
    NSString* encodedCalendar = FWXMLEncodeString(@"Internal Task Calendar");
    
    NSString* sql;
    if (plugin.isPocketInformant2)
        sql = @"select 2, (select max(Z_PK) from ZPICALCALENDARITEM), Z_UUID"
                " from Z_METADATA"
                " where Z_VERSION = (select max(Z_VERSION) from Z_METADATA)"; 
    else
        sql = @"select 1, max(id) from task"; 
    
    // Get the highest current task id
    PITaskIntegration* exec = [[PITaskIntegration alloc] init];
    NSArray* results = FWDBGetRecords(sql, kSQLMaxTaskID, nil, app.databasePath, app, exec);
    NSArray* info = [results objectAtIndex:0];
    NSNumber* maxKey = [info objectAtIndex:0];
    NSString* uuid = [info objectAtIndex:1];
    [exec release];
    
    NSDateFormatter *format = [[NSDateFormatter alloc] init];
    [format setTimeZone:[NSTimeZone timeZoneForSecondsFromGMT:0]];
    [format setDateFormat:@"yyyy-MM-dd'T'HH:mm:ss'Z'"];
    NSString* currentDate = [format stringFromDate:[NSDate date]]; 
    [format release];
    
    NSString* importanceCode;
    switch (importance) {
        case PIImportanceTop:
            importanceCode = @"3";//none
            break;
        case PIImportanceHigh:
            importanceCode = @"2";//ok
            break;
        case PIImportanceNormal:
            importanceCode = @"1";//med
            break;
        case PIImportanceLow:
            importanceCode = @"0";//low
            break;
        default:
            importanceCode = @""; //low
            break;
    }
    
//<data><task id="x-coredata://FC27C93A-5A04-44F9-877C-0186FFEC3035/PICalTask/p31"><modified>2001-01-01T00:00:00Z</modified><startdate>2011-09-25T00:00:00Z</startdate><duedate>2011-09-25T00:00:00Z</duedate><alarmMinutes/><projectName>Inbox</projectName><teamtask>NO</teamtask><completed>NO</completed><percentcomplete>0</percentcomplete><priority>0</priority><priorityandrank/><sensitivity>0</sensitivity><status>0</status><title>Test</title><note/><color>#000000</color><categories>Internal Task Calendar</categories><repeat>0</repeat><recurrence><data></data></recurrence></task></data>    
    
    if (plugin.isPocketInformant2) {
        NSString* params = [NSString stringWithFormat:@"<data>"
                            "<task id=\"x-coredata://%@/PICalTask/p%d\">"
                            "<modified>%@</modified>"
                            "<startdate/>"
                            "<duedate/>"
                            "<alarmMinutes/>"
                            "<projectName>Inbox</projectName>"
                            "<teamtask>NO</teamtask>"
                            "<completed>NO</completed>"
                            "<percentcomplete>0</percentcomplete>"
                            "<priority>%@</priority>"
                            "<priorityandrank/>"
                            "<sensitivity>0</sensitivity>"
                            "<status>0</status>"
                            "<title>%@</title>"
                            "<note></note>"
                            "<color>#000000</color>"
                            "<categories>%@</categories>"
                            "<repeat>0</repeat>"
                            "<recurrence><data></data></recurrence>"
                            "</task>"
                            "</data>",
                            uuid,
                            [maxKey intValue] + 1,
                            currentDate,
                            importanceCode,
                            encodedTitle,
                            encodedCalendar];
        
        NSString* url = [NSString stringWithFormat:@"informant://createTask?%@",
                         FWBase64EncodeString(params)];
    
        return url;
    }
    else {
        NSString* params = [NSString stringWithFormat:@"<data>"
                            "<task id=\"%i\">"
                            "<title>%@</title>"
                            "<modified>%@</modified>"
                            "<startdate/><duedate/>"
                            "<teamtask>false</teamtask>"
                            "<completed>NO</completed>"
                            "<percentcomplete>0</percentcomplete>"
                            "<priority>%@</priority>"
                            "<priorityandrank/>"
                            "<sensitivity>0</sensitivity>"
                            "<status>0</status>"
                            "<note></note>"
                            "<color>#000000</color>"
                            "<categories>%@</categories>"
                            "<repeat>0</repeat>"
                            "<recurrence><data></data></recurrence>"
                            "</task>"
                            "</data>",
                            [maxKey intValue] + 1,
                            encodedTitle,
                            currentDate,
                            importanceCode,
                            encodedCalendar];
        
        NSString* url = [NSString stringWithFormat:@"informant://createTask?%@",
                         FWBase64EncodeString(params)];
        
        return url;
    }
}

- (NSObject*)sqlMaxTaskID:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSInteger key = sqlite3_column_int(sqlStatement, 1);
    NSString* uuid = version == 1 ? @"" : FWDBReadColumnString(sqlStatement, 2);
    
    return [NSArray arrayWithObjects:
            [NSNumber numberWithInt:key],
            uuid,
            nil];
}


- (NSObject*)sqlNotesForTask:(sqlite3_stmt *)sqlStatement application:(FWApplication *)application
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSString* notes = FWDBReadColumnString(sqlStatement, 1);
    
    return notes;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Database support

- (NSObject*)objectForSQLRecord:(sqlite3_stmt *)sqlStatement withId:(NSInteger)sqlid context:(NSObject*)context application:(FWApplication *)application
{
    if (sqlid == kSQLBasicTaskList)
        return [self sqlBasicTaskList:sqlStatement application:application];
    else if (sqlid == kSQLAddChildData)
        return [self sqlAddChildDataToTaskList:sqlStatement application:application];
    else if (sqlid == kSQLAugmentedTask)
        return [self sqlAugmentedBasicTask:sqlStatement application:application];
    else if (sqlid == kSQLArrayOfContexts)
        return [self sqlArrayOfContexts:sqlStatement application:application];
    else if (sqlid == kSQLMaxTaskID)
        return [self sqlMaxTaskID:sqlStatement application:application];
    else if (sqlid == kSQLNotes)
        return [self sqlNotesForTask:sqlStatement application:application];
    else
        return nil;
}

@end
